package com.xz.group12306.business.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.xz.group12306.domain.Ticket;
import com.xz.group12306.domain.User;
import com.xz.group12306.utils.ServiceException;



/**
 * 数据库工具类
 */
public class BaseDao {
		/**
		 * 获取数据库连接
		 */
		public static Connection getConnection() {
			Connection conn = null;
			/*
			try {
				Class.forName("oracle.jdbc.OracleDriver");
				String url = "jdbc:oracle:thin:@localhost:1521:orcl";
				String user = "scott";
				String password = "tiger";
				conn = DriverManager.getConnection(url, user, password);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new ServiceException("Can not get connection", e);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				throw new ServiceException("Can not get connection", e);
			}*/
			
			try {
				Context context = new InitialContext();
				DataSource ds = (DataSource) context
						.lookup("java:comp/env/jdbc/Our12306");
				conn = ds.getConnection();
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				throw new ServiceException("Can not get connection", e);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				throw new ServiceException("Can not get connection", e);
			}

			return conn;
		}

		/**
		 * 开启事务
		 * 
		 * @param conn
		 */
		public static void beginTransaction(Connection conn) {
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				throw new ServiceException("Can not begin transaction", e);
			}
		}

		/**
		 * 提交事务
		 * 
		 * @param conn
		 */
		public static void commit(Connection conn) {
			try {
				conn.commit();
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				throw new ServiceException("Can not commit transaction", e);
			}
		}

		/**
		 * 回滚事务
		 * 
		 * @param conn
		 */
		public static void rollback(Connection conn) {
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				throw new ServiceException("Can not rollback transaction", e);
			}
		}

		/**
		 * 关闭连接
		 * 
		 * @param conn
		 */
		public static void closeConnection(Connection conn) {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new ServiceException("Can not close connection", e);
			}
		}

		/**
		 * 关闭statement
		 * 
		 * @param stmt
		 */
		public static void closeStatement(ResultSet rs, Statement stmt) {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				throw new ServiceException("Can not close statement", e);
			}
		}
	
	//修改数据
    public int executeUpdate(String preparedSql,Object param[]){
    	int num=0;//接收数据库受影响的行数
    	PreparedStatement pstmt=null;
    	Connection conn = getConnection();//连接数据库
    	try {
			pstmt=conn.prepareStatement(preparedSql);
			//处理占位符
			if(pstmt!=null){
				for (int i = 0; i < param.length; i++) {
					//为预编译的sql设置参数
					pstmt.setObject(i+1, param[i]);
				}
			}
			num=pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("执行sql出错");
			e.printStackTrace();
		}finally {
			BaseDao.closeConnection(conn);
		}  	
		return num;  	
    }
    
    
    //查询用户数据
    public User executeOneUser(String preparedSql,Object param[]){
    	User user=null;
    	ResultSet rs =null;
    	PreparedStatement pstmt=null;
    	Connection conn = getConnection();//连接数据库
		try {
			pstmt=conn.prepareStatement(preparedSql);
			//处理占位符
			if(pstmt!=null){
				for (int i = 0; i < param.length; i++) {
					//为预编译的sql设置参数
					pstmt.setObject(i+1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));
                user.setBirthday(rs.getDate("birthday"));
				user.setContent(rs.getString("content"));
				user.setImagePath(rs.getString("image_path"));
				user.setCert(rs.getString("cert"));
				user.setUserType(rs.getString("userType"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeStatement(rs, pstmt);
			BaseDao.closeConnection(conn);
		}

		return user;
    	
    }
    
  //查询车票数据
    public Ticket executeOneTicket(String preparedSql,Object param[]){
    	Ticket ticket=null;
    	ResultSet rs =null;
    	PreparedStatement pstmt=null;
    	Connection conn = getConnection();//连接数据库
		try {
			pstmt=conn.prepareStatement(preparedSql);
			//处理占位符
			if(pstmt!=null){
				for (int i = 0; i < param.length; i++) {
					//为预编译的sql设置参数
					pstmt.setObject(i+1, param[i]);
				}
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				ticket=new Ticket();
				ticket.setTicketID(rs.getInt("TicketID"));
				ticket.setDepature(rs.getString("Depature"));
				ticket.setDestinction(rs.getString("Destinction"));
				ticket.setTrainNo(rs.getString("TrainNo"));
				ticket.setStartTime(rs.getDate("StartTime"));
				ticket.setTicketNo(rs.getInt("TicketNo"));
				ticket.setCostTime(rs.getString("CostTime"));
				ticket.setPrice(rs.getString("price"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeStatement(rs, pstmt);
			BaseDao.closeConnection(conn);
		}

		return ticket;
    	
    }
    
    

}
